#!/usr/bin/python
# -*- coding: UTF-8 -*-

#被执行人
t_zhixing= '''
SELECT 
id,
key_no,
company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
zx_id,
trim(case when substr(t.name,0,1)='“' then translate(translate(regexp_replace(t.name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.name,length(t.name),1)='、' then translate(translate(regexp_replace(t.name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.name,length(t.name),1)=',' then translate(translate(regexp_replace(t.name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as name, 
from_utc_timestamp(lian_date,'yyyy-MM-dd HH:mm:ss') as lian_date,
(case when (instr(an_no,'（') >0 and instr(an_no,'）') >0 and instr(an_no,'号') >0) then an_no
      when (instr(an_no,'(') >0 and instr(an_no,')') >0 and instr(an_no,'号') >0 ) then an_no
      when (instr(an_no,'[') >0 and instr(an_no,']') >0 and instr(an_no,'号') >0 ) then an_no
      when (instr(an_no,'【') >0 and instr(an_no,'】') >0 and instr(an_no,'号') >0 ) then an_no
      else '' end ) as an_no,
(case when (instr(execute_gov,'人民') >0 and instr(execute_gov,'法院') <0 and instr(execute_gov,'法庭') <0 
and instr(execute_gov,'审判庭') <0 and instr(execute_gov,'委员会') <0 and instr(execute_gov,'分院') <0) 
    then trim(regexp_replace(execute_gov,'法','法院'))
    when instr(execute_gov,'人民') >0  then trim(execute_gov)
    when instr(execute_gov,'法院') >0  then trim(execute_gov)
    when instr(execute_gov,'法庭') >0  then trim(execute_gov)
    when instr(execute_gov,'审判庭') >0 then trim(execute_gov)
    when instr(execute_gov,'委员会') >0 then trim(execute_gov)
    when instr(execute_gov,'分院') >0 then trim(execute_gov)
    when instr(t.execute_gov,'院') >0  then trim(t.execute_gov)
    when instr(t.execute_gov,'局') >0  then trim(t.execute_gov)  
else '' end ) as execute_gov,
regexp_replace(biaodi,'[^0-9.-]+','') as biaodi,
status,
is_valid,
(case when (length(partycard_num)=10 and substr(partycard_num,-2,1)='-') then partycard_num
    when (length(partycard_num)=9) then concat(substring(partycard_num,1,8),'-',substr(partycard_num,-1,1))
    when (length(partycard_num)=15 and rlike(partycard_num,'[0-9A-Za-z\\-]')) then partycard_num
    when (length(partycard_num)=17 and rlike(partycard_num,'[0-9A-Za-z\\-]')) then partycard_num
    when (length(partycard_num)=18 and rlike(partycard_num,'[0-9A-Za-z\\-]')) then partycard_num
    when (length(partycard_num)=19 and rlike(partycard_num,'[0-9A-Za-z\\-]')) then partycard_num
    when (length(partycard_num)=20 and rlike(partycard_num,'[0-9A-Za-z\\-]')) then partycard_num
    else '' end ) as partycard_num,
name_and_key_no,
org_type,
'zjs' as data_source
FROM  qcc_t_zhixing t
'''

#失信信息
t_shixin='''
select 
t.id as id,
t.key_no as key_no,
t.company_id as company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
t.sx_id as sx_id,
t.name as name,
from_utc_timestamp(t.lian_date,'yyyy-MM-dd HH:mm:ss') as lian_date,
(case when (instr(t.an_no,'（') >0 and instr(t.an_no,'）') >0 and instr(t.an_no,'号') >0 ) then t.an_no
      when (instr(an_no,'(') >0 and instr(an_no,')') >0 and instr(an_no,'号') >0 ) then an_no
      when (instr(t.an_no,'[') >0 and instr(t.an_no,']') >0 and instr(t.an_no,'号') >0 ) then t.an_no
      when (instr(t.an_no,'【') >0 and instr(t.an_no,'】') >0 and instr(t.an_no,'号') >0 ) then t.an_no
      else '' end) as an_no,
(case when (length(t.org_no)=10 and substr(t.org_no,-2,1)='-') then t.org_no
when (length(t.org_no)=10 and substr(t.org_no,-2,1)!='-') then concat(substring(t.org_no,1,9),'-',substr(t.org_no,-1,1))
when length(t.org_no)=9 then concat(t.org_no,'-')
when (length(t.org_no)=15 and instr(t.org_no,'-') >-1) then t.org_no
when (length(t.org_no)=17 and instr(t.org_no,'-') >-1) then t.org_no
when (length(t.org_no)=18 and instr(t.org_no,'-') >-1) then t.org_no
when (length(t.org_no)=19 and instr(t.org_no,'-') >-1) then t.org_no
when (length(t.org_no)=20 and instr(t.org_no,'-') >-1) then t.org_no
else '' end ) as org_no,
t.owner_name as owner_name,
(case when (instr(t.execute_gov,'人民') >0 and instr(t.execute_gov,'法院') <0 and instr(t.execute_gov,'法庭') <0 and instr(t.execute_gov,'审判庭') <0
and instr(t.execute_gov,'委员会') <0 and instr(t.execute_gov,'分院') <0) then trim(regexp_replace(t.execute_gov,'法','法院'))
    when (instr(t.execute_gov,'人民') >0)  then trim(t.execute_gov)
    when (instr(t.execute_gov,'法院') >0)  then trim(t.execute_gov)
    when (instr(t.execute_gov,'法庭') >0)  then trim(t.execute_gov)
    when (instr(t.execute_gov,'审判庭') >0)  then trim(t.execute_gov)
    when (instr(t.execute_gov,'委员会') >0)  then trim(t.execute_gov)
    when instr(t.execute_gov,'院') >0  then trim(t.execute_gov)
    when instr(t.execute_gov,'局') >0  then trim(t.execute_gov)  
    else '' end) as execute_gov,
province,
t.execute_unite as execute_unite,
trim(t.yiwu) as yiwu,
(case when (trim(t.execute_status)='部分未履行') then t.execute_status when (trim(t.execute_status)='全部未履行') then t.execute_status when (trim(t.execute_status)='') then t.execute_status else '' end ) as execute_status,
trim(t.actionremark) as actionremark,
from_utc_timestamp(t.public_date,'yyyy-MM-dd HH:mm:ss') as public_date,
(case when (t.age > '0' and t.age <= '200') then t.age else '' end) as age,
(case when instr(t.sexy,'男') >0 then '男' when instr(t.sexy,'女') >0  then '女' when instr(t.sexy,'其他') >0  then '其他' else '' end) as sexy,
(case when (instr(t.execute_no,'（') >0 and instr(t.execute_no,'）') >0 and instr(t.execute_no,'号') >0 ) then t.execute_no
    when (instr(t.execute_no,'[') >0 and instr(t.execute_no,']') >0 and instr(t.execute_no,'号') >0 ) then t.execute_no
    when (instr(t.execute_no,'【') >0 and instr(t.execute_no,'】') >0 and instr(t.execute_no,'号') >0 ) then t.execute_no
    else '' end) as execute_no,
trim(t.performedpart) as performedpart,
trim(t.unperformpart) as unperformpart,
t.is_valid as is_valid,
t.org_type as org_type,
'zjs' as data_source
FROM  qcc_t_shixin t'''


# 裁判文书
t_judgement='''
select 
t.id as id,
t.key_no as key_no,
t.company_id as company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
t.case_id as case_id,
t.court as court,
trim(regexp_replace(t.case_name,'[`~!#$^*+=?！@￥%…‘；’。，？]','')) as case_name,
(case when (instr(t.case_no,'（') >0 and instr(t.case_no,'）') >0 and instr(t.case_no,'号') >0 ) then t.case_no
      when (instr(t.case_no,'(') >0 and instr(t.case_no,')') >0 and instr(t.case_no,'号') >0 ) then t.case_no
      when (instr(t.case_no,'[') >0 and instr(t.case_no,']')>0 and instr(t.case_no,'号') >0 ) then t.case_no
      when (instr(t.case_no,'【') >0 and instr(t.case_no,'】') >0 and instr(t.case_no,'号') >0 ) then t.case_no
      else '' end) as case_no,
(case when instr(t.case_type,'民事案件') >0 then 'ms'
 when instr(t.case_type,'刑事案件') >0 then 'xs'
 when instr(t.case_type,'行政案件') >0 then 'xz'
 when instr(t.case_type,'知识产权') >0 then 'zscq'
 when instr(t.case_type,'国家赔偿与司法救助案件') >0 then 'pc'
 when instr(t.case_type,'执行案件') >0 then 'zx'
 when instr(t.case_type,'管辖案件') >0 then 'gx'
 when instr(t.case_type,'区际司法协助案件') >0 then 'qsx'
 when instr(t.case_type,'国际司法协助案件') >0 then 'gsx'
 when instr(t.case_type,'司法制裁案件') >0 then 'zc'
 when instr(t.case_type,'非诉保全审查案件') >0 then 'bq'
 when (t.case_type = 'ms' OR t.case_type = 'xs' OR t.case_type = 'xz'  OR t.case_type = 'zscq'  OR t.case_type = 'pc' 
 OR t.case_type = 'zx' OR t.case_type = 'gx' OR t.case_type = 'qsx'  OR t.case_type = 'gsx' OR t.case_type = 'zc' 
 OR t.case_type = 'bq') then t.case_type
 else 'other' end) as case_type,
t.case_role as case_role,
t.case_reason as case_reason,
t.case_reason_type as case_reason_type,
(case when(t.court_year <= 2021 and t.court_year >0) then t.court_year
else '' end) as court_year,
t.court_month as court_month,
(case when length(court_level)>0 then court_level
when instr(t.court,'最高') >0 then '5'
 when instr(t.court,'高级') >0 then '4'
 when instr(t.court,'中级') >0 then '3'
 when (instr(t.court,'人民') >0 and instr(t.court,'最高') <0 and instr(t.court,'高级') <0 and instr(t.court,'中级') <0 ) then '2'
 when (instr(t.court,'法院') >0 and instr(t.court,'人民')<0) then '1'
else '' end) as court_level,
from_utc_timestamp(t.submit_date,'yyyy-MM-dd HH:mm:ss') as submit_date,
t.province,
t.is_valid as is_valid,
t.defendant as defendant,
t.prosecutor as prosecutor,
from_utc_timestamp(t.update_date,'yyyy-MM-dd HH:mm:ss') as update_date,
t.amount as amount,
'zjs' as data_source
from qcc_t_judgement t'''

# 裁判文书详情
t_judgement_detail= '''
select 
case_id,
trim(case when case_name rlike '（\\d{4}）\\s\\S*号'  then '' else case_name end) as case_name,
(case when (instr(case_no,'（') >0 and instr(case_no,'）') >0 and instr(case_no,'号') >0 ) then case_no
when (instr(case_no,'[') >0 and instr(case_no,']') >0 and instr(case_no,'号') >0 ) then case_no
when (instr(case_no,'【') >0 and instr(case_no,'】') >0 and instr(case_no,'号') >0 ) then case_no
else '' end) as case_no,
(case when instr(case_type,'民事案件') >0 then 'ms'
 when instr(case_type,'刑事案件') >0 then 'xs'
 when instr(case_type,'行政案件') >0 then 'xz'
 when instr(case_type,'知识产权') >0 then 'zscq'
 when instr(case_type,'国家赔偿与司法救助案件') >0 then 'pc'
 when instr(case_type,'执行案件') >0 then 'zx'
 when instr(case_type,'管辖案件') >0 then 'gx'
 when instr(case_type,'区际司法协助案件') >0 then 'qsx'
 when instr(case_type,'国际司法协助案件') >0 then 'gsx'
 when instr(case_type,'司法制裁案件') >0 then 'zc'
 when instr(case_type,'非诉保全审查案件') >0 then 'bq'
 when (case_type = 'ms' OR case_type = 'xs' OR case_type = 'xz'  OR case_type = 'zscq'  OR case_type = 'pc' 
 OR case_type = 'zx' OR case_type = 'gx' OR case_type = 'qsx'  OR case_type = 'gsx' OR case_type = 'zc' 
 OR case_type = 'bq') then case_type
else 'other' end) as case_type,
content,
court,
from_utc_timestamp(create_date,'yyyy-MM-dd HH:mm:ss') as create_date,
from_utc_timestamp(submit_date,'yyyy-MM-dd HH:mm:ss') as submit_date,
from_utc_timestamp(update_date,'yyyy-MM-dd HH:mm:ss') as update_date,
appellor,
from_utc_timestamp(judge_date,'yyyy-MM-dd HH:mm:ss') as judge_date,
case_reason,
trial_round,
defendant_list,
prosecutor_list,
is_valid,
content_clear,
fmt_content,
court_notice_list,
related_companies,
'zjs' as data_source
from qcc_t_judgement_detail t'''

# 法院公告
t_court= '''
select 
    t.id as id,
    t.key_no as key_no ,
    t.company_id as company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
    t.court_id as court_id,
    trim(t.party) as party,
    (case when ((instr(t.category,'起诉状') >0 OR instr(t.category,'上诉状') >0) and instr(t.category,'开庭传票') >0 ) then '起诉状、上诉状副本'
     when (instr(t.category,'开庭传票') >0 and instr(t.category,'起诉')<0 and instr(t.category,'上诉')<0) then '开庭传票'
     when (instr(t.category,'起诉') >0 and instr(t.category,'开庭传票') >0) then '起诉状副本及开庭传票'
     when (instr(t.category,'送达') >0 and instr(t.category,'公告') >0) then '送达公告'
    else t.category end) as category,
    from_utc_timestamp(t.submit_date,'yyyy-MM-dd HH:mm:ss') as submit_date,
    from_utc_timestamp(t.publish_date,'yyyy-MM-dd HH:mm:ss') as publish_date,
    t.publish_page,
    trim(case when (instr(t.court,'人民') >0 and instr(t.court,'法院') <0 and instr(t.court,'法庭') <0 and instr(t.court,'审判庭') <0
        and instr(t.court,'委员会') <0 and instr(t.court,'分院') <0) then regexp_replace(t.court,'法','法院')
        when (instr(t.court,'人民')>0) then t.court
        when (instr(t.court,'法院')>0) then t.court
        when (instr(t.court,'法庭')>0) then t.court
        when (instr(t.court,'审判庭')>0) then t.court
        when (instr(t.court,'委员会')>0) then t.court
        when instr(t.court,'院') >0  then trim(t.court)
        when instr(t.court,'局') >0  then trim(t.court)  
        else '' end) as court,
    trim(t.content) as content,
    province,
    t.is_valid as is_valid,
    (case when (t.court_year<= 2021) then t.court_year  else '' end) as court_year,
    t.name_key_no_collection as name_key_no_collection,
    t.prosecutor_list as prosecutor_list,
    t.defendant_list as defendant_list,
'zjs' as data_source
    from qcc_t_court t'''

# 开庭公告
t_courtnotice= '''
select 
    t.id as id,
    t.key_no as key_no,
    t.company_id as company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
    t.court_notice_id as court_notice_id,
    from_utc_timestamp(t.lian_date,'yyyy-MM-dd HH:mm:ss') as lian_date,
    t.case_reason as case_reason,
    t.prosecutor_list as prosecutor_list,
    t.defendant_list as defendant_list,
    trim(case when (instr(t.execute_gov,'人民') >0 and instr(t.execute_gov,'法院') <0 and instr(t.execute_gov,'法庭') <0 and instr(t.execute_gov,'审判庭') <0
        and instr(t.execute_gov,'委员会') <0 and instr(t.execute_gov,'分院') <0) then regexp_replace(t.execute_gov,'法','法院')
    when (instr(t.execute_gov,'人民') >0)  then t.execute_gov
    when (instr(t.execute_gov,'法院') >0)  then t.execute_gov
    when (instr(t.execute_gov,'法庭') >0)  then t.execute_gov
    when (instr(t.execute_gov,'审判庭') >0)  then t.execute_gov
    when (instr(t.execute_gov,'委员会') >0)  then t.execute_gov
    when instr(t.execute_gov,'院') >0  then trim(t.execute_gov)
    when instr(t.execute_gov,'局') >0  then trim(t.execute_gov)
    else '' end) as execute_gov,
    (case when (instr(t.case_no,'（') >0 and instr(t.case_no,'）') >0 and instr(t.case_no,'号') >0 ) then t.case_no
    when (instr(t.case_no,'[') >0 and instr(t.case_no,']') >0 and instr(t.case_no,'号') >0 ) then t.case_no
    when (instr(t.case_no,'【') >0 and instr(t.case_no,'】') >0 and instr(t.case_no,'号') >0 ) then t.case_no
    else '' end) as case_no,
    province,
    t.is_valid as is_valid,
    t.prosecutor as prosecutor,
    t.defendant as defendant,
    from_utc_timestamp(t.execute_unite,'yyyy-MM-dd HH:mm:ss') as execute_unite,
    from_utc_timestamp(t.schedule_time,'yyyy-MM-dd HH:mm:ss') as schedule_time,
    t.undertake_department as undertake_department,
    trim(regexp_replace(t.chief_judge,'[~!/@#$%^&￥*-_=+\\\\|[{}];:\\'\\',<.>/?‘；”“’。，、？]+','')) as chief_judge,
'zjs' as data_source
    from qcc_t_courtnotice t'''

# 送达公告
t_delivery_notice= '''
select 
    id,
    key_no,
    company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
    delivery_id,
    sn,
    an_no,
    trim(translate(translate(regexp_replace(title,'[`~!#$^*+=:;,./?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')')) as title,
    (case when (instr(court_name,'人民') >0 and instr(court_name,'法院') <0 and instr(court_name,'法庭') <0 
    and instr(court_name,'审判庭') <0 and instr(court_name,'委员会') <0 and instr(court_name,'分院') <0) 
    then trim(regexp_replace(court_name,'法','法院'))
    when instr(court_name,'人民') >0  then trim(court_name)
    when instr(court_name,'法院') >0  then trim(court_name)
    when instr(court_name,'法庭') >0  then trim(court_name)
    when instr(court_name,'审判庭') >0  then trim(court_name)
    when instr(court_name,'委员会') >0  then trim(court_name)
    when instr(court_name,'院') >0  then trim(court_name)
    when instr(court_name,'局') >0  then trim(court_name)
    else '' end) as court_name,
    from_utc_timestamp(publish_date,'yyyy-MM-dd HH:mm:ss') as publish_date,
'zjs' as data_source
    from qcc_t_delivery_notice t'''

# 立案信息
t_register = '''
select
    id,
    key_no,
    company_id,
trim(case when substr(t.companyname,0,1)='“' then translate(translate(regexp_replace(t.companyname,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.companyname,length(t.companyname),1)='、' then translate(translate(regexp_replace(t.companyname,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.companyname,length(t.companyname),1)=',' then translate(translate(regexp_replace(t.companyname,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.companyname,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as companyname, 
    register_id,
    (case when (instr(case_no,'（') >0 and instr(case_no,'）') >0 and instr(case_no,'号') >0 ) then case_no
    when (instr(case_no,'[') >0 and instr(case_no,']') >0 and instr(case_no,'号') >0 ) then case_no
    when (instr(case_no,'【') >0 and instr(case_no,'】') >0 and instr(case_no,'号') >0 ) then case_no
    else '' end) as case_no,
    from_utc_timestamp(punish_date,'yyyy-MM-dd HH:mm:ss') as punish_date,
    prosecutor,
    appellee,
'zjs' as data_source
    from qcc_t_register t
'''

# 立案详情
t_register_detail = '''
select
    id,
    reason,
    (case when (instr(case_no,'（') >0 and instr(case_no,'）') >0 and instr(case_no,'号') >0 ) then case_no
          when (instr(case_no,'(') >0 and instr(case_no,')') >0 and   instr(case_no,'号') >0 ) then case_no
          when (instr(case_no,'[') >0 and instr(case_no,']') >0 and instr(case_no,'号') >0 ) then case_no
          when (instr(case_no,'【') >0 and instr(case_no,'】') >0 and instr(case_no,'号') >0 ) then case_no
    else '' end) as case_no,
    from_utc_timestamp(regist_date,'yyyy-MM-dd HH:mm:ss') as regist_date,
    from_utc_timestamp(hold_date,'yyyy-MM-dd HH:mm:ss') as hold_date,
    from_utc_timestamp(finish_date,'yyyy-MM-dd HH:mm:ss') as finish_date,
    department,
    (case when (instr(court,'人民') >0 and instr(court,'法院') <0 and instr(court,'法庭') <0 and instr(court,'审判庭')<0
    and instr(court,'委员会') <0 and instr(court,'分院') <0) then trim(regexp_replace(court,'法','法院'))
    when instr(court,'人民') >0  then trim(court)
    when instr(court,'法院') >0  then trim(court)
    when instr(court,'法庭') >0  then trim(court)
    when instr(court,'审判庭') >0  then trim(court)
    when instr(court,'委员会') >0  then trim(court)
    when instr(court,'院') >0  then trim(court)
    when instr(court,'局') >0  then trim(court)
    else '' end) as court,
    trim(regexp_replace(judger,'[~!/@#$%^&￥*-_=+（）[{}];:\'\',<>/?‘；”“’。，、？]+','')) as judger,
    trim(regexp_replace(assistant,'[~!/@#$%^&￥*-_=+（）[{}];:\'\',<>/?‘；”“’。，、？]+','')) as assistant,
    (case 
    when case_type='' then case_type
    when (instr(case_type,'管辖') >0 or instr(case_type,'gx') >0) then '管辖'
    when instr(case_type,'制裁') >0 then '司法制裁'
    when (instr(case_type,'执行') >0 or instr(case_type,'zx') >0) then '执行'
    when instr(case_type,'其他') >0 then '其他'
    when instr(case_type,'赔偿') >0 then '国家赔偿与司法救济'
    when (instr(case_type,'民事') >0 or instr(case_type,'ms') >0) then '民事'
    when instr(case_type,'保全') >0 then '非诉保全审查'
    when (instr(case_type,'国际') >0 and instr(case_type,'司法协助') >0) then '国际司法协助'
    when instr(case_type,'行政') >0 then '行政'
    when (instr(case_type,'区') >0 and instr(case_type,'司法协助') >0) then '区际司法协助'
    when instr(case_type,'刑事') >0 then '刑事'
    else '' end) as case_type,
    case_status,
    prosecutor,
    appellee,
    outsider,
'zjs' as data_source
    from qcc_t_register_detail t
'''

#司法协助
t_eci_assistance ='''
select
    id,
    key_no,
    company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
    trim(translate(translate(regexp_replace(executed_by,'[`~!#$^*+=:?！@￥…‘’。？]',""),'（','('),'）',')'))  as executed_by,
    trim(regexp_replace(equity_amount,'[^0-9\\\\u4e00-\\\\u9fa5\\.]','')) as equity_amount,
    (case when (instr(enforcement_court,'人民') >0 and instr(enforcement_court,'法院') <0 and instr(enforcement_court,'法庭') <0 and 
    instr(enforcement_court,'审判庭')<0 and instr(enforcement_court,'委员会') <0 and instr(enforcement_court,'分院') <0) 
    then trim(regexp_replace(enforcement_court,'法','法院'))
    when instr(enforcement_court,'人民') >0  then trim(enforcement_court)
    when instr(enforcement_court,'法院') >0  then trim(enforcement_court)
    when instr(enforcement_court,'法庭') >0  then trim(enforcement_court)
    when instr(enforcement_court,'审判庭') >0  then trim(enforcement_court)
    when instr(enforcement_court,'委员会') >0  then trim(enforcement_court)
    when instr(enforcement_court,'院') >0  then trim(enforcement_court)
    when instr(enforcement_court,'局') >0  then trim(enforcement_court)
    else '' end) as enforcement_court,
(case when (instr(execution_notice_num,'（') >0 and instr(execution_notice_num,'）') >0 and instr(execution_notice_num,'号') >0 ) then execution_notice_num
      when (instr(execution_notice_num,'(') >0 and instr(execution_notice_num,')') >0 and   instr(execution_notice_num,'号') >0 ) then execution_notice_num
      when (instr(execution_notice_num,'[') >0 and instr(execution_notice_num,']') >0 and instr(execution_notice_num,'号') >0 ) then execution_notice_num
      when (instr(execution_notice_num,'【') >0 and instr(execution_notice_num,'】') >0 and instr(execution_notice_num,'号') >0 ) then execution_notice_num
      else '' end) as execution_notice_num,
    status,
'zjs' as data_source
    from qcc_t_eci_assistance t
'''

#司法协助_股权冻结
t_eci_assistance_equity_freeze ='''
select
    id,
    key_no,
    company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
    trim(translate(translate(regexp_replace(scompany_name,'[`~!#$^*+=:?！@￥…‘’。？]',""),'（','('),'）',')'))  as scompany_name,
    execution_matters,
    execution_doc_num,
    (case when (instr(execution_verdict_num,'（') >0 and instr(execution_verdict_num,'）') >0 and instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    when (instr(execution_verdict_num,'(') >0 and instr(execution_verdict_num,')') >0 and   instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    when (instr(execution_verdict_num,'[') >0 and instr(execution_verdict_num,']') >0 and instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    when (instr(execution_verdict_num,'【') >0 and instr(execution_verdict_num,'】') >0 and instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    else '' end) as execution_verdict_num,
    executed_person_doc_type,
    executed_person_doc_num,
    from_utc_timestamp(freeze_start_date,'yyyy-MM-dd HH:mm:ss') as freeze_start_date,
    from_utc_timestamp(freeze_end_date,'yyyy-MM-dd HH:mm:ss') as freeze_end_date,
    freeze_term,
    from_utc_timestamp(public_date,'yyyy-MM-dd HH:mm:ss') as public_date,
'zjs' as data_source
    from qcc_t_eci_assistance_equity_freeze t
'''

#司法协助_解除冻结
t_eci_assistance_equity_unfreeze = '''
select
    id,
    key_no,
    company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
     when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
     else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
    execution_matters,
(case when (instr(execution_verdict_num,'（') >0 and instr(execution_verdict_num,'）') >0 and   instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    when (instr(execution_verdict_num,'(') >0 and instr(execution_verdict_num,')') >0 and   instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    when (instr(execution_verdict_num,'[') >0 and instr(execution_verdict_num,']') >0 and instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    when (instr(execution_verdict_num,'【') >0 and instr(execution_verdict_num,'】') >0 and instr(execution_verdict_num,'号') >0 ) then execution_verdict_num
    else '' end) as execution_verdict_num,
(case when (instr(execution_doc_num,'（') >0 and instr(execution_doc_num,'）') >0 and  instr(execution_doc_num,'号') >0 ) then execution_doc_num
    when (instr(execution_doc_num,'(') >0 and instr(execution_doc_num,')') >0 and   instr(execution_doc_num,'号') >0 ) then execution_doc_num
    when (instr(execution_doc_num,'[') >0 and instr(execution_doc_num,']') >0 and instr(execution_doc_num,'号') >0 ) then execution_doc_num
    when (instr(execution_doc_num,'【') >0 and instr(execution_doc_num,'】') >0 and instr(execution_doc_num,'号') >0 ) then execution_doc_num
    else '' end) as execution_doc_num,
    executed_person_doc_type,
    executed_person_doc_num,
    from_utc_timestamp(un_freeze_date,'yyyy-MM-dd HH:mm:ss') as un_freeze_date,
    from_utc_timestamp(public_date,'yyyy-MM-dd HH:mm:ss') as public_date,
    thaw_organ,
    (case when (instr(thaw_doc_no,'（') >0 and instr(thaw_doc_no,'）') >0 and  instr(thaw_doc_no,'号') >0 ) then thaw_doc_no
        when (instr(thaw_doc_no,'(') >0 and instr(thaw_doc_no,')') >0 and   instr(thaw_doc_no,'号') >0 ) then thaw_doc_no
        when (instr(thaw_doc_no,'[') >0 and instr(thaw_doc_no,']') >0 and instr(thaw_doc_no,'号') >0 ) then thaw_doc_no
        when (instr(thaw_doc_no,'【') >0 and instr(thaw_doc_no,'】') >0 and instr(thaw_doc_no,'号') >0 ) then thaw_doc_no
        else '' end) as thaw_doc_no,
'zjs' as data_source
    from qcc_t_eci_assistance_equity_unfreeze t
'''

#司法协助_股东变更
t_eci_assistance_judicial_partners_change = '''
select
id,
key_no,
company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
 when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
 when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
 else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
execution_matters,
execution_verdict_num,
executed_person_doc_type,
executed_person_doc_num,
assignee,
assist_exec_date,
assignee_doc_kind,
assignee_reg_no,
stock_company_name,
'zjs' as data_source
from qcc_t_eci_assistance_judicial_partners_change t
'''

#法律诉讼统计
t_lawsuit_count = '''
select
company_id,
trim(case when substr(t.company_name,0,1)='“' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
 when substr(t.company_name,length(t.company_name),1)='、' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；”“’。，、？]',""),'（','('),'）',')') 
 when substr(t.company_name,length(t.company_name),1)=',' then translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=,?！@￥%…‘；”“’。，？]',""),'（','('),'）',')') 
 else translate(translate(regexp_replace(t.company_name,'[`~!#$^*+=?！@￥%…‘；’。？]',""),'（','('),'）',')')  end ) as company_name, 
zhixing_count,
shixin_count,
judgement_count,
court_count,
courtnotice_count,
delivery_notice_count,
assistance_count,
register_count,
'zjs' as data_source
from qcc_t_lawsuit_count t
'''